オンプレミスデータをRedshiftにAmazon S3経由でインポートする
渡辺です。
Redshiftでは、Amazon S3にアップロードしたCSVファイルをデータとしてロードすることができます。 今回のエントリーでは、Redshiftをセットアップし、データをロードするまでの手順を紹介したいと思います。
今回紹介する手順は次のようになります。
- データファイルを準備する
- データファイルをAmazon S3にアップロードする
- セキュリティグループを作成する
- Redshiftのクラスタを作成する
- クライアント用EC2を作成する
- クライアント用EC2からRedshiftに接続し、Amazon S3からデータをコピーする
なお、このエントリーで紹介している手順にある操作における細かいオプションや、他の方法などについては、Amazon Redshift DB開発者ガイド – データのロード処理(2).COPYコマンドの使用などを参照してください。
データファイルの準備
はじめにRedshiftに投入するデータを格納するテーブルを設計し、データを準備します。
テーブルの設計
今回は次のような売上情報を扱うテーブル（sales）を作成します。
create table sales( timestamp DATETIME, shop VARCHAR(128), item VARCHAR(128), username VARCHAR(32), unit_price SMALLINT, quantity SMALLINT ) DISTKEY(timestamp) SORTKEY(timestamp, shop, item);
Redshiftは列指向のデータベースですが、PostgreSQLを利用して作られています。クエリなどに制約はありますが、簡単なテーブルであればPostgreSQLと同じように扱うことができるでしょう。
Redshiftでは大量のデータを複数の領域に分散して格納します。この時、分散させる基準となるのがDISTKEY（分散キー）です。今回ではtimestampをDISTKEYに設定しています。したがって、近い日時のデータは同じ領域に格納されることになります。これらはクエリーの実行効率に影響を与えるため、適切に設計しなければなりません。同様にSORTKEY（ソートキー）も何を分析したいかによって適切に設計する必要があります。
詳細は、Amazon Redshift DB開発者ガイド – テーブル設計のベストプラクティスを参照ください。
データファイルの準備
今回は、オンプレミスのRDBから売上データを抽出し、分析のためにRedshiftにインポートするといった状況を想定します。そこで、一旦CSVファイルに売上データを抽出し、Amazon S3にアップロードし、RedshiftのCOPYコマンドでデータをロードさせる方法を選択します。
RDBからデータをCSVファイルに抽出したならば、Amazon S3にアップロードします。ただし、効率よくロードするために幾つかの前処理を行います。
sales.txt
2014-02-01 10:10:31.460000000,XYZ Shop,スピーカー,u00001,3000,1 2014-02-01 20:46:43.300000000,ABC Market,時計,u00002,12000,1 2014-02-02 12:25:10.160000000,ABC Market,X Book,u00001,3000,1 2014-02-02 15:02:08.210000000,ABC Market,レトルトカレー,u00004,96,2
なお、COPYコマンドはUTF-8以外のマルチバイト文字には対応していないため、UTF-8でCSVファイルを作成してください。
ファイルの分割
Redshiftでは分割ファイルの並列ロードを行うことができます。 このため、データファイルを同程度のサイズの複数ファイルに分割します。 この時、分割するファイル数はRedshiftのスライスの数とします。 スライスとは、Redshiftの内部に配置されたメモリやディスクの領域です。 スライス数はノードのコア数に対応しているため、2コアのノードを選択した場合はスライス数も2です。 今回は一番小さなdw1.xlargeをシングルノードとして使うのでスライス数は2、すなわちCSVファイルは2つに分割します。
sales.txt.1
2014-02-01 10:10:31.460000000,XYZ Shop,スピーカー,u00001,3000,1 2014-02-01 20:46:43.300000000,ABC Market,時計,u00002,12000,1
sales.txt.2
2014-02-02 12:25:10.160000000,ABC Market,X Book,u00001,3000,1 2014-02-02 15:02:08.210000000,ABC Market,レトルトカレー,u00004,96,2
今回はサンプルと言うことでデータ量は少なくなっていますが、実際のデータではそれぞれが数万件程度あると思ってください。
ファイルの圧縮
COPYコマンドはファイル圧縮に対応しています。
小さなファイルであれば、CSVファイルをそのままAmazon S3にアップロードしても良いのですが、通常はかなり大きなファイルとなることが想定されるので圧縮してアップロードしましょう。 通信料もAmazon S3の利用料も節約することができます。ZGIP形式に対応しているので、gzipコマンドで圧縮します。
$ gzip sales.txt.*
Amazon S3へのデータファイルアップロード
AWS Management Consoleを開き、S3のメニューから適当なバケットを作成し、それぞれのファイルをアップロードします。 同一フォルダにアップロードすれば、既存のバケットでも構いません。
セキュリティグループの作成
次にRedshiftクラスタやEC2インスタンスを作成していきますが、はじめにセキュリティグループを作成しておきましょう。
AWS Management ConsoleのVPCメニューから、Redshiftクラスタを配置する予定のVPCにセキュリティグループを作成します。 Redshiftはデフォルトで5439ポートを使うため、5439ポートのInboundアクセスを許可しておきます。
Redshiftクラスタの作成
AWS Management ConsoleのRedshiftメニューからクラスタをLaunchします。
Cluster Identifier、Database Name等は適当に設定してください。
今回は、Node Typeはdw1.xlarge、Cluster TypeはSingle Node(コンピュートノード数は1）と最小構成としています。
最後にクラスタを配置するVPCやセキュリティグループなどを選択すればクラスタの設定は完了です。 なお、Single Nodeの場合、クラスタが起動するまでは2-3分程度でした。起動したならば、クラスタのエンドポイントを確認しておきましょう。
クライアント用EC2の作成
最後にRedshiftに接続してコマンドの実行やメンテナンスを行うためのEC2インスタンスを作成します。 このインスタンスは基本的にメンテナンス用なので、高いスペックは不要です。
IAM Roleの作成
COPYコマンドを使う時、Amazon S3にアクセスする必要があります。 アクセスキーとシークレットキーを生で使用することもできますが、ここではセキュリティを高めるため、IAM Roleを利用した一時的認証情報を利用します（後述）。 Amazon S3にアクセス可能なIAM Roleを作成してください。
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:Get*",
"s3:List*"
],
"Resource": "*"
}
]
}
EC2インスタンスの作成
先ほど作成したIAM RoleでEC2インスタンスを起動します。 インスタンスタイプはt1.microで充分です。
インスタンスが起動したならば、SSHで接続します。
psqlのインストール
クライアント用EC2からはpsqlコマンドでRedshiftにアクセスします。 はじめにyumコマンドでpsqlをインストールしてください。
$ sudo yum -y install postgresql9
クライアントとして利用するだけですから、postgresql9-serverは不要です。
一時的なセキュリティ認証情報の取得
この後、psqlコマンドでRedshiftに接続し、COPYコマンドによりAmazon S3にアクセスしてデータをロードします。 この時、Amazon S3にアクセスするための認証情報（アクセスキー）が必要です。 IAM Role名を指定しメタ情報を取得し、一時的なセキュリティ認証情報を取得しましょう。
$ GET http://169.254.169.254/latest/meta-data/iam/security-credentials/<IAM_ROLE_NAME>
{
"Code" : "Success",
"LastUpdated" : "2014-02-05T04:15:13Z",
"Type" : "AWS-HMAC",
"AccessKeyId" : <ACCESS_KEY>,
"SecretAccessKey" : <SCECRET_KEY>,
"Token" : <TOKEN>,
"Expiration" : "2014-02-05T10:40:10Z"
}
EC2インスタンスメタデータの取得についての詳細は、こちらのドキュメントを参照してください。
Redshiftへの接続
準備はできたのでRedshiftに接続します。
psqlコマンドを使い、Redshiftのエンドポイントに接続します。 コマンドのパラメータとして、ホスト名・ポート番号・ユーザ名・データベース名を指定してください。
$ psql -h <HOST_NAME> -p 5439 -U <USER_NAME> <DATABASE_NAME>
接続できない場合はセキュリティグループの設定などを確認しましょう。
テーブルの作成
最初に示したcreate tableのSQLを実行し、テーブルの作成します。
example=# create table sales( example(# timestamp DATETIME, example(# shop VARCHAR(128), example(# item VARCHAR(128), example(# username VARCHAR(32), example(# unit_price SMALLINT, example(# quantity SMALLINT example(# ) example-# DISTKEY(timestamp) example-# SORTKEY(timestamp, shop, item); CREATE TABLE example=# select * from sales; timestamp | shop | item | username | unit_price | quantity -----------+------+------+----------+------------+---------- (0 行)
データのロード
COPYコマンドを使い、データをロードします。 次のようにCSVオプションとGZIPオプションを指定し、先ほど取得した一時的なセキュリティ認証情報を付与します。
example-# COPY sales FROM 's3://<BUCKET_PATH>/' CSV GZIP example-# credentials 'aws_access_key_id=<ACCESS_KEY>;aws_secret_access_key=<SCECRET_KEY>;token=<TOKEN>'; INFO: Load into table 'sales' completed, 4 record(s) loaded successfully.
確認してみます。
example=# select * from sales;
timestamp | shop | item | username | unit_price | quantity
------------------------+------------+----------------+----------+------------+----------
2014-02-01 10:10:31.46 | XYZ Shop | スピーカー | u00001 | 3000 | 1
2014-02-02 15:02:08.21 | ABC Market | レトルトカレー | u00004 | 96 | 2
2014-02-01 20:46:43.30 | ABC Market | 時計 | u00002 | 12000 | 1
2014-02-02 12:25:10.16 | ABC Market | X Book | u00001 | 3000 | 1
(4 行)
データは期待通りに投入されているようですね。
まとめ
今回は、CSVデータをAmazon S3にアップロードし、Redshiftにロードするまでの手順を紹介しました。 オンプレミスのデータでAWS環境への持ち出しが難しいケースもあるかと思いますが、一度データを加工した上で分析用のデータのみをAWS環境に持ち出すことも簡単に出来ます。 今回は利用しませんでしたが、データの暗号化にも対応しています。
そして、Redshiftに投入したビッグデータはTableauなどの分析ツールを使い可視化することができます（Amazon RedshiftとTableauによるビッグデータ分析）。 AWSを利用することで、コスト効率よく分析ツールを使うことができるでしょう。 興味があれば是非お問い合わせください！
なお、このエントリーを参考にして試しにRedshiftを起動した場合は、試した後に落とすのを忘れないようにしてくださいね。
![[AWS Technical Support Note]ต้องระบุ endpoint url ด้วย DNS name อะไร หากต้องการเข้าถึง S3 โดยใช้ CLI ผ่าน S3 interface endpoint](https://devio2024-media.developers.io/image/upload/v1730273469/user-gen-eyecatch/uqmw2ndhu2v0te66pt7o.webp)
